#include <QSqlQuery>
#include <QSqlRecord>
#include <QVariant>
#include <QMessageBox>
#include "category.h"
#include "categorydao.h"
#include <QDebug>

MoneyCategory* searchForCategory(QList<MoneyCategory*> listMoneyCategory, int idCategory)
{
    qDebug() << __PRETTY_FUNCTION__;
    foreach (MoneyCategory* category, listMoneyCategory) {
        if (category->id() == idCategory) {
            return category;

        }
    }

    return 0;
}

MoneyCategory* getCategory(MoneyCategory* incomplete_cat, int id_user)
{
    qDebug() << __PRETTY_FUNCTION__;

    QString strQuery = " SELECT id_category, name, description as id_category, name, description FROM "
                       " category where id_user = :id_user AND name LIKE :name_cat "
                       " AND description = :desc_cat ";

    QSqlQuery query;
    query.prepare(strQuery);
    query.bindValue(":name_cat", incomplete_cat->name());
    query.bindValue(":desc_cat", incomplete_cat->description());
    query.bindValue(":id_user", id_user);
    query.exec();
    MoneyCategory* cat;
    if (query.next()) {
        cat = new MoneyCategory(query.value(query.record().indexOf("id_category")).toInt(),
                                 query.value(query.record().indexOf("name")).toString(),
                                 query.value(query.record().indexOf("description")).toString(),
                                 false);
        QString strQuery02 = " SELECT COUNT(m1.*), COUNT(m2,*) as count_in, count_out FROM money m1, money m2 "
                             " WHERE m1.id_user = :id_user AND m2.id_user = :id_user "
                             " AND m1.id_category = :id_category AND m2.id_category = :id_category "
                             " AND m1.type_money = 0 AND m2.type_money = 1";

        QSqlQuery query02;
        query02.prepare(strQuery02);
        query02.bindValue(":id_category", cat->id());
        query02.bindValue(":id_user", id_user);
        query02.exec();

        if (query02.next()) {
            cat->setTotalExpenses(query02.value(query.record().indexOf("count_out")).toFloat());
            cat->setTotal(query02.value(query.record().indexOf("count_in")).toFloat() -
                          query02.value(query.record().indexOf("count_out")).toFloat());
        }

    } else {
        qDebug() << __PRETTY_FUNCTION__ << "category wasn't found";
    }

    return cat;
}

MoneyCategory* getTotalByCategory(MoneyCategory* moneyCategory, int id_user)
{
    if (!moneyCategory)
        return 0;

    float total_expenses = 0;
    float total = 0;

    // Getting Total Expenses
    QString strQueryTotalOUT = " SELECT SUM(m1.amount)as count_out FROM money m1, money_category mcat1 "
                              " WHERE m1.id_user = :id_user AND m1.id_money = mcat1.id_money "
                              " AND mcat1.id_category = :id_category AND m1.type_money = 1 ";

    QSqlQuery query02;
    query02.prepare(strQueryTotalOUT);
    query02.bindValue(":id_category", moneyCategory->id());
    query02.bindValue(":id_user", id_user);
    query02.exec();

    if (query02.next())
        total_expenses = query02.value(query02.record().indexOf("count_out")).toFloat();

    // Getting Total IN
    QString strQueryTotalIN = " SELECT SUM(m1.amount)as count_in FROM money m1, money_category mcat1 "
                              " WHERE m1.id_user = :id_user AND m1.id_money = mcat1.id_money "
                              " AND mcat1.id_category = :id_category AND m1.type_money = 0 ";

    QSqlQuery query03;
    query03.prepare(strQueryTotalIN);
    query03.bindValue(":id_category", moneyCategory->id());
    query03.bindValue(":id_user", id_user);
    query03.exec();

    if (query03.next())
        total = query03.value(query03.record().indexOf("count_in")).toFloat() - total_expenses;

    moneyCategory->setTotal(total);
    moneyCategory->setTotalExpenses(total_expenses);

    return moneyCategory;
}

QList<MoneyCategory*> getAllMoneyCategory(int id_user)
{
    qDebug() << __PRETTY_FUNCTION__;

    QString strQuery = " SELECT id_category, name, description as id_category, name, description FROM ";
    strQuery = strQuery + " category where id_user = :id_user";

    QSqlQuery query(strQuery);
    query.bindValue(":id_user", id_user);

    QList<MoneyCategory*> listMoneyCategory;
    query.exec();
    while (query.next()) {
        MoneyCategory* moneyCategory = new MoneyCategory(query.value(query.record().indexOf("id_category")).toInt(),
                                    query.value(query.record().indexOf("name")).toString(),
                                    query.value(query.record().indexOf("description")).toString(),
                                    false);

        moneyCategory = getTotalByCategory(moneyCategory, id_user);

        listMoneyCategory.append(moneyCategory);
    }

    qDebug() << __PRETTY_FUNCTION__ << "Getting " << listMoneyCategory.size() << " categories";

    return listMoneyCategory;
}

bool deleteMoneyCategory(MoneyCategory* category)
{
    if (!category)
        return false;

    qDebug() << __PRETTY_FUNCTION__;

    QString strQuery = " DELETE FROM ";
    strQuery = strQuery + "category WHERE id_category = :id_category";

    QSqlQuery query;
    query.prepare(strQuery);
    query.bindValue(":id_category", category->id());
    return query.exec();
}

bool updateMoneyCategory(MoneyCategory* category)
{
    if (!category)
        return false;

    qDebug() << __PRETTY_FUNCTION__;
    QString strQuery = " UPDATE category SET name = :name, description = :description "
               " WHERE id_category = :id_category";


    QSqlQuery query;
    query.prepare(strQuery);
    query.bindValue(":id_category", category->id());
    query.bindValue(":name", category->name());
    query.bindValue(":description", category->description());

    return query.exec();
}

bool registerMoneyCategory(MoneyCategory* category, int id_user)
{
    if (!category)
        return false;
    qDebug() << __PRETTY_FUNCTION__;
    QString strQuery = " INSERT INTO category (name, description, id_user) values (:name, :description, :id_user) ";

    qDebug() << strQuery << id_user;

    QSqlQuery query;
    query.prepare(strQuery);
    query.bindValue(":name", category->name());
    query.bindValue(":description", category->description());
    query.bindValue(":id_user", id_user);

    return query.exec();
}
